package 反射6;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

/**
 * 实现数据库的增删改查
 * @Auther:lele
 * @Date:2025/3/14
 * Api:
 *      1.getConnection() 连接数据库   return Connection
 *      2.pre(String sql,Object...parameters)    创建预编译对象 return PreparedStatement
 *          sql:比如 insert into user value(?,?,?)
 *          parameters：？的值
 *      3.update(String sql,Object...p)  能实现查找、修改、插入的功能    return int返回受影响的行数
 *          sql:比如 insert into user value(?,?,?)
 *          parameters：？的值
 *      4.queryBean(String sql,Class<T> cls,Object...parameters)  获得数据
 *
 */
public class  MyhandleDbReflection {

    private static String url;
    private static String user;
    private static String  password;
    private static String driver;

    static{

//        InputStream resourceAsStream = MyhandleDbReflection.class.getClassLoader().getResourceAsStream("jdbc.properties");
//        Properties properties = new Properties();
//        try {
//            properties.load(resourceAsStream);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        url = properties.getProperty("url");
//        user = properties.getProperty("user");
//        password = properties.getProperty("password");
//        driver = properties.getProperty("driver");

    }
//连接
    public static Connection getConnection() throws Exception {
//        return DriverManager.getConnection(url,user,password);
        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("src/main/resources/jdbc.properties");
        properties.load(fileInputStream);

//        使用德鲁伊数据库连接池技术
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("connection = " + connection);
        return connection;
    }

//    预编译
    public static PreparedStatement pre(String sql,Object...parameters) throws Exception {
        Connection connection =MyhandleDbReflection.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (parameters != null && parameters.length > 0) {
            for (int i = 0; i < parameters.length; i++) {
                preparedStatement.setObject(i + 1, parameters[i]);
            }
        }
        return preparedStatement;
    }

//    更新插入数据

    public static void insert(String sql,Object...p) throws Exception {
        System.out.println("update(sql,p) = " + update(sql, p));

    }
//    插入修改数据
    public static int update(String sql,Object...p) throws Exception {
        int f=0;
        PreparedStatement preparedStatement = pre(sql,p);
//        获取字符串中参数个数
        ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
        int parameterCount = parameterMetaData.getParameterCount();
        if (parameterCount!=0){

            for (int i = 0; i < parameterCount; i++) {
                preparedStatement.setObject(i+1,p[i]);
            }
        }
         f = preparedStatement.executeUpdate();
        close(getConnection(), preparedStatement.getResultSet(), preparedStatement);

        return f;
    }


//取出一个数据
    public static <T> T queryBean(String sql,Class<T> cls,Object...parameters) throws Exception {
//        定义需要的对象
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        T t=null;

//        连接数据库
        connection = getConnection();
         preparedStatement = connection.prepareStatement(sql);

        if (parameters != null && parameters.length > 0) {
            for (int i = 0; i < parameters.length; i++) {
                preparedStatement.setObject(i + 1, parameters[i]);
            }
        }

        // 执行查询
        resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();


//        ResultSetMetaData metaData = resultSet.getMetaData();
//        System.out.println("metaData = " + metaData);
        resultSet.next();
        t=cls.getConstructor().newInstance();
//        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <=columnCount; i++) {
            BeanUtils.setProperty(t,metaData.getColumnName(i),resultSet.getObject(i));
        }
//        close(getConnection(),resultSet,preparedStatement);

        return t;
    }


    public static <T> T queryBean1(String sql,Class<T> cls,Object...parameters) throws Exception {
//        定义需要的对象
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        T t=null;

//        连接数据库
        connection = getConnection();
        preparedStatement = connection.prepareStatement(sql);
        ArrayList<Object> objects = new ArrayList<>();


        if (parameters != null && parameters.length > 0) {
            for (int i = 0; i < parameters.length; i++) {
                preparedStatement.setObject(i + 1, parameters[i]);
            }
        }

        // 执行查询
        resultSet = preparedStatement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();


//        ResultSetMetaData metaData = resultSet.getMetaData();
//        System.out.println("metaData = " + metaData);
        resultSet.next();
        t=cls.getConstructor().newInstance();
//        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <=columnCount; i++) {
            BeanUtils.setProperty(t,metaData.getColumnName(i),resultSet.getObject(i));
            objects.add(t);
        }
        System.out.println("objects = " + objects);

        close(getConnection(),resultSet,preparedStatement);
        return t;
    }

//    1、获取表中某一条记录，以hashMap形式(键值对)存储返回

    /**
     *
     * @param sql
     * @param paramaters
     * @return
     * @throws Exception
     */
    public static HashMap<String,Object> mapQuery(String sql,Object...paramaters) throws Exception {
        HashMap<String, Object> stringObjectHashMap = null;

            PreparedStatement pre = pre(sql,paramaters);
//            PreparedStatement preparedStatement = pre.prepareStatement(sql);
//            for (int i = 0; i < paramaters.length; i++) {
//                preparedStatement.setObject(i + 1, paramaters[i]);
            ResultSet resultSet = pre.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            stringObjectHashMap = new HashMap<>();

            resultSet.next();

            for (int j = 0; j < columnCount; j++) {
                stringObjectHashMap.put(metaData.getColumnName(j+1),resultSet.getObject(j+1));
            }
        close(getConnection(),resultSet,pre);
        return stringObjectHashMap;
    }


    //2.  把多个hashmap对象封装到list集合中

    /**
     *
     * @param sql
     * @param parameters
     * @return
     * @throws Exception
     */
    public static List<Object> mapQueryList(String sql,Object...parameters) throws Exception {
        ArrayList<Object> objects = new ArrayList<>();

        PreparedStatement pre = pre(sql,parameters);
        ResultSet resultSet = pre.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){
            HashMap<String, Object> stringObjectHashMap = new HashMap<>();
            for (int j = 0; j < columnCount; j++) {
                stringObjectHashMap.put(metaData.getColumnName(j+1),resultSet.getObject(j+1));
            }
            objects.add(stringObjectHashMap);
        }
        close(getConnection(),resultSet,pre);
        return objects;
    }

    //3.把一个数据以数组的形式返回

    /**
     *
     * @param sql
     * @param parameters
     * @return
     * @throws Exception
     */
    public static Object[] quaryList(String sql,Object...parameters) throws Exception {

        PreparedStatement pre = pre(sql,parameters);
        ResultSet resultSet = pre.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        Object []o = new Object[columnCount];
        resultSet.next();
            for (int j = 0; j < columnCount; j++) {
                o[j] = resultSet.getObject(j + 1);
            }
        close(getConnection(),resultSet,pre);
            return o;

    }

    //把一个数据以数组的形式放到集合中
    public static List<Object[]> quaryLists(String sql,Object...parameters) throws Exception {
        ArrayList<Object[]> objects = new ArrayList<>();
        PreparedStatement pre = pre(sql,parameters);
        ResultSet resultSet = pre.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()){
            Object []o = new Object[columnCount];
            for (int j = 0; j < columnCount; j++) {
                o[j] = resultSet.getObject(j + 1);
            }
            objects.add(o);
        }
        return  objects;
    }

//关闭资源
    public static void close(Connection conn,ResultSet resultSet,PreparedStatement preparedStatement){
        try {
            if (conn!=null){
                conn.close();
            }
            if (resultSet!=null){
                resultSet.close();
            }
            if (preparedStatement!=null){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}





